import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

public class DatabaseUtilities {
    static public Connection db;
    static private Map<String, Long> bloggerUserNameToId = new HashMap<String, Long>();
    static private Map<String, Long> blogUrlToId = new HashMap<String, Long>();
    
    static private long maxBloggerId = -1;
    static private long maxBlogId = -1;
    
    static public void initializeDatabase(String dbName, String user, String password, boolean initialize) throws IOException, SQLException {
    	try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {
            // handle the error
        }
    	
    	db = DriverManager.getConnection("jdbc:mysql://localhost/" + dbName + 
    		"?user=" + user +
    		"&password=" + password +
    		"&useUnicode=yes" +
    		"&characterEncoding=UTF-8");
    	if (initialize) {
    		initialize();
    	}
        
        PreparedStatement stmt;
    	ResultSet rs = null;
    	
    	stmt = db.prepareStatement("SET character_set_client=utf8");
        try {
        	stmt.executeQuery();
        } finally {
        	stmt.close();
        }
        stmt = db.prepareStatement("SET character_set_connection=utf8");
        try {
        	stmt.executeQuery();
        } finally {
        	stmt.close();
        }
        stmt = db.prepareStatement("SET character_set_results=utf8");
        try {
        	stmt.executeQuery();
        } finally {
        	stmt.close();
        }

        stmt = db.prepareStatement("SELECT id, username FROM bloggers");
        try {
        	rs = stmt.executeQuery();
            while (rs.next()) {
                long id = rs.getLong(1);
                bloggerUserNameToId.put(rs.getString(2), id);
                maxBloggerId = Math.max(maxBloggerId, id);
            }
        } finally {
        	if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) { } // ignore
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) { } // ignore
                stmt = null;
            }
        }
        
        stmt = db.prepareStatement("SELECT id, url FROM blogs");
        try {
        	rs = stmt.executeQuery();
            while (rs.next()) {
                long id = rs.getLong(1);
                blogUrlToId.put(rs.getString(2), id);
                maxBlogId = Math.max(maxBlogId, id);
            }
        } finally {
        	if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) { } // ignore
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) { } // ignore
                stmt = null;
            }
        }
    }

    static public long getBloggerId(String bloggerUsername, boolean create, String gender) throws SQLException {
        if (bloggerUserNameToId.containsKey(bloggerUsername)) {
            return bloggerUserNameToId.get(bloggerUsername);
        } else if (create) {
            PreparedStatement stmt = db.prepareStatement("INSERT INTO bloggers (id, username, gender) VALUES(?, ?, ?)");
            try {
                long id = ++maxBloggerId;
                stmt.setLong(1, id);
                stmt.setString(2, bloggerUsername);
                stmt.setString(3, gender);
                stmt.execute();
                
                bloggerUserNameToId.put(bloggerUsername, id);
                return id;
            } finally {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) { } // ignore
            }
        }
        return -1;
    }
    
    static public long getBlogId(String blogUrl, long bloggerId, boolean create) throws SQLException {
        if (blogUrlToId.containsKey(blogUrl)) {
            return blogUrlToId.get(blogUrl);
        } else if (create) {
            PreparedStatement stmt = db.prepareStatement("INSERT INTO blogs (id, url, blogger_id) VALUES(?, ?, ?)");
            try {
                long id = ++maxBlogId;
                stmt.setLong(1, id);
                stmt.setString(2, blogUrl);
                stmt.setLong(3, bloggerId);
                stmt.execute();
                
                blogUrlToId.put(blogUrl, id);
                return id;
            } finally {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) { } // ignore
            }
        }
        return -1;
    }
    
    static private void initialize() throws SQLException {
    	Statement stmt = db.createStatement();
    	String charset = "CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'";
    	try {
	    	stmt.execute("CREATE TABLE IF NOT EXISTS bloggers (" +
	    		"id BIGINT PRIMARY KEY, username TEXT, gender TEXT)");
	    	stmt.execute("CREATE TABLE IF NOT EXISTS blogs (" +
	    		"id BIGINT PRIMARY KEY  NOT NULL, " +
	    		"blogger_id BIGINT NOT NULL, " +
	    		"url TEXT NOT NULL, " +
	    		"earliest_post_unix_time INTEGER, " +
	    		"latest_post_unix_time INTEGER)");
	    	stmt.execute("CREATE TABLE IF NOT EXISTS posts (" +
	    		"id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL UNIQUE, " +
	    		"blog_id BIGINT NOT NULL, " +
	    		"title TEXT " + charset + ", " +
	    		"date INTEGER, " +
	    		"level TEXT, " +
	    		"body_html MEDIUMTEXT " + charset + ", " +
	    		"body_text MEDIUMTEXT " + charset + ", " +
	    		"paragraph_count INTEGER NOT NULL, " +
	    		"sentence_count INTEGER NOT NULL, " +
	    		"word_count INTEGER NOT NULL, " +
	    		"character_count INTEGER NOT NULL, " +
	    		"url TEXT)");
	    	stmt.execute("CREATE TABLE IF NOT EXISTS sentences (" +
	    		"word_count INTEGER, " +
	    		"id BIGINT PRIMARY KEY, " +
	    		"in_paragraph_index INTEGER, " +
	    		"in_post_index INTEGER, " +
	    		"original_text VARCHAR(1000) " + charset + ", " +
	    		"paragraph_index INTEGER, " +
	    		"segmented_text VARCHAR(2000) " + charset + ", " +
	    		"tagged_text VARCHAR(5000) " + charset + ", " +
	    		"tree VARCHAR(5000) " + charset + ", " +
	    		"post_id BIGINT)");
	    	stmt.execute("CREATE TABLE IF NOT EXISTS words (" +
	    		"id BIGINT PRIMARY KEY AUTO_INCREMENT, " +
	    		"in_sentence_token_index INTEGER, " +
	    		"pos_tag TEXT, " +
	    		"sentence_id BIGINT, " +
	    		"word TEXT " + charset + ", " +
	    		"word_length INTEGER NOT NULL)");
	    	stmt.execute("CREATE TABLE IF NOT EXISTS followings (" +
	    		"sentence_id BIGINT, " +
	    		"word_before_id BIGINT, " +
	    		"word_after_id BIGINT, " +
	    		"word_before TEXT " + charset + ", " +
	    		"word_after TEXT " + charset + ", " +
	    		"pos_tag_before TEXT, " +
	    		"pos_tag_after TEXT)");
    	} finally {
            try {
                stmt.close();
            } catch (SQLException sqlEx) { } // ignore
    	}
    }
}
